<?php


namespace App\Admin\Services;

use App\Jobs\SyncMwsJob;
use App\Model\AmazonGood;
use App\Model\ExportGoodJob;
use App\Model\ExportGoodJobDetail;
use App\Model\Good;
use App\Model\GoodNote;
use App\Model\Note;
use App\Model\Stock;
use Encore\Admin\Auth\Database\Administrator;
use Encore\Admin\Facades\Admin;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Support\Facades\DB;
use PhpOffice\PhpSpreadsheet\Exception;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;


class GoodService
{

    /**
     * @desc 获取商品列表
     *
     * @param array $columns
     * @param array $order
     * @param int $pageSize
     * @param int $offset
     * @return array
     *
     * @author: 梅杰[meijie3169@dingtalk.com] at 2020-03-17 13:49:20
     */
    public function getGoodsList(array $columns, array $order, int $pageSize = 10, int $offset = 0)
    {
        $builder = Good::query()->join('amazon_goods', 'amazon_goods.id', '=', 'amazon_goods_id')
            ->leftJoin('good_note', 'goods_id', '=', 'goods.id')
            ->select([
                'goods.*',
                'amazon_goods.asin',
                'amazon_goods.sku',
                'amazon_goods.fnsku',
                'admin_note',
                'custom_note'
            ]);
        foreach ($columns as &$column) {
            if (empty($column['name']) || !isset($column['search']['value'])) {
                continue;
            }
            if ($column['name'] == "id") {
                $column['name'] = "goods.id";
            }
            $builder->where($column['name'], 'like', "%" . escapeLikeStr($column['search']['value']) . "%");
        }

        // 处理排序
        foreach ($order as $k => $value) {
            $builder->orderBy($columns[$value['column']]['name'], $value['dir']);
        }
        $total = $builder->count();

        $data = $builder->orderByDesc('goods.id')
            ->take($pageSize)
            ->offset($offset)
            ->get();

        return [
            'total' => $total,
            'data' => $data
        ];
    }


    /**
     * @desc 设置备注
     *
     * @param int $GoodId
     * @param string $content
     *
     * @return bool
     *
     * @author: 梅杰[meijie3169@dingtalk.com] at 2020-03-17 15:15:56
     */
    public function setRemark(int $GoodId, string $content = null)
    {
        $update = [
            'goods_id' => $GoodId,
        ];
        if (Admin::user()->inRoles(['susan', 'administrator'])) {
            $update['admin_note'] = $content ?? '';
        } else {
            $update['custom_note'] = $content ?? '';
        }

        DB::beginTransaction();
        try {
            GoodNote::query()->updateOrInsert(['goods_id' => $GoodId], $update);
            Note::query()->insert([
                'goods_id' => $GoodId,
                'content' => $content ?? '',
                'user_user_id' => Admin::user()->id,
                'create_time' => date('Y-m-d H:i:s')
            ]);
        } catch (\Exception $exception) {
            DB::rollBack();
            dd($exception->getMessage());
            return false;
        }
        DB::commit();
        return true;
    }


    /**
     * @desc 库存操作
     *
     * @param int $goodId
     * @param int $value
     * @param int $type
     *
     * @return bool
     *
     * @author: 梅杰[meijie3169@dingtalk.com] at 2020-03-18 09:46:38
     */
    public function updateStock(int $goodId, int $value, int $type)
    {
        $stockModel = new Stock();
        $stockModel->fill([
            'goods_id' => $goodId,
            'value' => $value,
            'user_user_id' => Admin::user()->id,
            'type' => $type
        ]);
        DB::beginTransaction();
        try {
            if (!$type) {
                Good::query()->where('id', $goodId)
                    ->decrement('stock', $value);
            } else {
                Good::query()->where('id', $goodId)
                    ->increment('stock', $value);
            }
            $stockModel->save();
        } catch (\Exception $exception) {
            DB::rollBack();
            return false;
        }
        DB::commit();
        return true;
    }


    /**
     * 库存导出所需的数据
     *
     * @return Builder[]|Collection
     */
    public static function getStockExportData()
    {
        return Good::query()->with([
            'amazonGood:id,asin,sku,fnsku',
            'supplier:id,username',
            'lastAdminNote',
            'lastCustomNote',
            'totalStock'
        ])->orderByDesc('id')->get([
            'id',
            'amazon_goods_id',
            'casku',
            'uksku',
            'jpsku',
            'web_sku',
            'color',
            'number',
            'supplier_id'
        ]);
    }

    /**
     * 通过asin数组获取商品
     * @param array $asin
     * @return Builder[]|Collection
     *
     * @author: 梅杰 2020/2/19 21:39
     */
    public static function getGoodByAsin(array $asin)
    {
        return Good::query()
            ->join('amazon_goods', 'amazon_goods_id', '=', 'amazon_goods.id')
            ->whereIn('asin', $asin)
            ->with([
                'lastAdminNote',
                'lastCustomNote',
                'totalStock'
            ])
            ->select([
                'goods.id as id',
                'uksku',
                'web_sku',
                'ausku',
                'jpsku',
                'casku',
                'goods_img_url',
                'amazon_img_url',
                'asin',
                'sku',
                'fnsku'
            ])->get();

    }

    /**
     * 根据任务导出商品信息
     *
     * @param int $jobId
     * @return mixed
     *
     * @author: 梅杰 2020/2/23 18:35
     */
    public function exportGood(int $jobId)
    {
        $goods = $this->getGoodExportByJobId($jobId);
        $operator = ExportGoodJob::query()->find($jobId, ['user_id']);
        $permission = Administrator::query()
            ->find($operator->user_id)
            ->inRoles(['susan', 'administrator']);

        $spreadsheet = new Spreadsheet();
        try {
            $excelSheet = $spreadsheet->getActiveSheet();
        } catch (Exception $e) {
            return ExportGoodJob::query()->where('id', $jobId)->update([
                'is_over' => 2,
            ]);
        }
        $objPHPExcel = $excelSheet->setCellValue("A1", 'USASIN')
            ->setCellValue("B1", 'usfnsku')
            ->setCellValue("C1", 'ussku')
            ->setCellValue("D1", 'CASKU')
            ->setCellValue("E1", 'UKSKU')
            ->setCellValue("F1", 'JPSKU')
            ->setCellValue("G1", 'AUSKU')
            ->setCellValue("H1", 'WEB_SKU')
            ->setCellValue("I1", 'Amazon图片')
            ->setCellValue("J1", '导入的图片')
            ->setCellValue("K1", '当前库存')
            ->setCellValue("L1", '客服备注');


        if ($permission) {
            $objPHPExcel->setCellValue("M1", '管理员备注');
            $objPHPExcel->setCellValue("N1", '货号');
            $objPHPExcel->setCellValue("O1", '颜色');
            $objPHPExcel->setCellValue("P1", '供应商');
        }


        $objPHPExcel->getColumnDimension('A')->setWidth(12);
        $objPHPExcel->getColumnDimension('B')->setWidth(12);
        $objPHPExcel->getColumnDimension('C')->setWidth(15);

        $objPHPExcel->getColumnDimension('D')->setWidth(12);
        $objPHPExcel->getColumnDimension('E')->setWidth(12);
        $objPHPExcel->getColumnDimension('F')->setWidth(12);
        $objPHPExcel->getColumnDimension('G')->setWidth(12);

        $objPHPExcel->getColumnDimension('H')->setWidth(15);
        $objPHPExcel->getColumnDimension('I')->setWidth(15);

        $objPHPExcel->getColumnDimension('J')->setWidth(15);
        $objPHPExcel->getColumnDimension('K')->setWidth(20);
        $objPHPExcel->getColumnDimension('L')->setWidth(40);

        if ($permission) {
            $objPHPExcel->getColumnDimension('M')->setWidth(40);
            $objPHPExcel->getColumnDimension('N')->setWidth(12);
            $objPHPExcel->getColumnDimension('O')->setWidth(12);
            $objPHPExcel->getColumnDimension('P')->setWidth(12);
        }


        $k = 2;
        foreach ($goods as $good) {
            if (!$good->id) {
                $t = "(无效asin)";
            } else {
                $t = '';
            }

            $objPHPExcel->setCellValue('A' . $k, $good->asin . $t);
            $objPHPExcel->setCellValue('B' . $k, $good->fnsku);
            $objPHPExcel->setCellValue('C' . $k, $good->sku);

            $objPHPExcel->setCellValue('D' . $k, $good->casku);
            $objPHPExcel->setCellValue('E' . $k, $good->uksku);
            $objPHPExcel->setCellValue('F' . $k, $good->jpsku);
            $objPHPExcel->setCellValue('G' . $k, $good->ausku);
            $objPHPExcel->setCellValue('H' . $k, $good->web_sku);

            if ($good->amazon_image) {

                $objPHPExcel->getRowDimension($k)->setRowHeight(76);
                $objDrawing = new Drawing();
                $objDrawing->setResizeProportional(false);
                $objDrawing->setCoordinates("I$k");

                try {
                    $objDrawing->setPath(storage_path('app/' . $good->amazon_image));
                    $objDrawing->setWorksheet($excelSheet);
                } catch (Exception $e) {
                    $objPHPExcel->setCellValue('I' . $k, '图片插入失败：' . $e->getMessage());

                }
                $objDrawing->setOffsetX(8);
                $objDrawing->setOffsetY(8);

                $objDrawing->setHeight(80);
                $objDrawing->setWidth(80);

            } else {
                $objPHPExcel->setCellValue('I' . $k, '');
            }

            if ($good->goods_image) {
                $objPHPExcel->getRowDimension($k)->setRowHeight(76);

                $objDrawing = new Drawing();
                $objDrawing->setResizeProportional(false);
                $objDrawing->setCoordinates("J$k");

                try {
                    $objDrawing->setPath(storage_path('app/' . $good->goods_image));
                    $objDrawing->setWorksheet($excelSheet);
                } catch (Exception $e) {
                    $objPHPExcel->setCellValue('J' . $k, '图片插入失败：' . $e->getMessage());
                }
                $objDrawing->setOffsetX(8);
                $objDrawing->setOffsetY(8);

                $objDrawing->setHeight(80);
                $objDrawing->setWidth(80);

            } else {
                $objPHPExcel->setCellValue('J' . $k, '');
            }

            $objPHPExcel->setCellValue('K' . $k, $good->stock ?? 0);

            $objPHPExcel->setCellValue('L' . $k, $good->lastCustomNote->content ?? '');
            if ($permission) {
                $objPHPExcel->setCellValue('M' . $k, $good->lastAdminNote->content ?? '');
                $objPHPExcel->setCellValue('N' . $k, $good->number);
                $objPHPExcel->setCellValue('O' . $k, $good->color);
                $objPHPExcel->setCellValue('P' . $k, $good->supplier->username ?? '');
            }
            $k++;
        }

        try {
            $fileName = "good_info_$jobId" . date('YmdHis');
            $path = '/exportSuccess/' . $fileName . '.xlsx';
            $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
            $writer->save(public_path($path));
        } catch (\PhpOffice\PhpSpreadsheet\Writer\Exception $e) {
            return ExportGoodJob::query()->where('id', $jobId)->update([
                'is_over' => 2,
            ]);

        }

        return ExportGoodJob::query()->where('id', $jobId)->update([
            'is_over' => 1,
            'path' => $path
        ]);

    }

    /**
     * 通过JobId 获取导出的数据
     *
     * @param int $jobId
     * @return Builder[]|Collection
     *
     * @author: 梅杰 2020/2/23 19:25
     */
    public function getGoodExportByJobId(int $jobId)
    {
        return Good::query()
            ->join('amazon_goods', 'amazon_goods_id', '=', 'amazon_goods.id')
            ->rightJoin('good_job_detail', 'good_id', '=', 'goods.id')
            ->where('job_id', $jobId)
            ->with([
                'lastAdminNote',
                'lastCustomNote',
                'supplier:id,username'
            ])
            ->select([
                'goods.id as id',
                'goods_image',
                'amazon_image',
                'uksku',
                'jpsku',
                'casku',
                'number',
                'color',
                'stock',
                'ausku',
                'supplier_id',
                'good_job_detail.asin',
                'sku',
                'fnsku'
            ])->orderBy('sort')
            ->get();
    }

    /**
     * @param array $asin
     * @return Builder[]|Collection
     * @author: 梅杰 2020/2/23 18:06
     */
    public function getGoodsIdByAsin(array $asin)
    {
        return Good::query()
            ->join('amazon_goods', 'amazon_goods_id', '=', 'amazon_goods.id')
            ->whereIn('asin', $asin)
            ->select([
                'goods.id as good_id',
                'asin'
            ])->get();
    }

    /**
     * 同步amazon商品图
     *
     * @return bool
     */
    public static function syncMws()
    {
        AmazonGood::query()->with('good:id,amazon_goods_id')->select(['id', 'asin'])->chunk(5, function ($data) {
            $jobData = [];
            foreach ($data as $good) {
                if ($good->good) {
                    $jobData[$good->asin] = $good->good->id;
                }
            }

            if ($jobData) {
                dispatch((new SyncMwsJob($jobData)));
            }

        });

        return true;
    }


    /**
     * 转utf8
     *
     * @param $string
     * @return bool|false|string
     *
     * @author: 梅杰 2020/2/18 21:47
     *
     */
    public static function convertToUtf($string)
    {
        return iconv("GB2312", "UTF-8", $string);
    }
}
